﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Iacym.Entities;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Data.Objects;

namespace Iacym.Repository
{
    public class MatrimonioRepository:MasterRepository,IMatrimonioRepository
    {
        public List<MATRIMONIO> GetAllFromMatrimonio()
        {
            var query = from c in context.MATRIMONIOs.Include("PERSONA").Include("PERSONA1").Include("HIJOS.PERSONA")
                        select c;
            ObjectContext.Refresh(RefreshMode.StoreWins, query);
            return query.ToList();

        }

        public List<MATRIMONIO> GetFromMatrimonioByPersona(string descripcion)
        {
            if (descripcion != null)
            {
                var query = from c in context.MATRIMONIOs.Include("PERSONA").Include("PERSONA1").Include("HIJOS.PERSONA")
                            where c.PERSONA.ApeMaterno.Contains(descripcion) | c.PERSONA.ApePaterno.Contains(descripcion) | c.PERSONA.Nombres.Contains(descripcion) |
                                  c.PERSONA1.ApeMaterno.Contains(descripcion) | c.PERSONA1.ApePaterno.Contains(descripcion) | c.PERSONA1.Nombres.Contains(descripcion)
                            select c;

                return query.ToList();
            }
            else
            {
                var query = from c in context.MATRIMONIOs.Include("PERSONA").Include("PERSONA1").Include("HIJOS.PERSONA")
                        select c;
                return query.ToList();
            }
        }

        public void AdddMatrimonio(MATRIMONIO matrimonio)
        {
            foreach (var item in matrimonio.HIJOS)
            {
                item.PERSONA = null;
                context.Entry(item).State = EntityState.Added;
                context.HIJOS.Add(item);
            }

            matrimonio.PERSONA = null;
            matrimonio.PERSONA1 = null;
            context.MATRIMONIOs.Add(matrimonio);
            context.SaveChanges();
        }

        public void UpdateMatrimonio(MATRIMONIO matrimonio) 
        {
            //var matriBD = context.Database.SqlQuery<MATRIMONIO>("GetMatrimonioById @matrimonioId", new SqlParameter("@matrimonioId", matrimonio.Id)).SingleOrDefault();

            foreach (var item in matrimonio.HIJOS)
            {
                item.MATRIMONIO = null;
                item.PERSONA = null;
                if (item.Numero == 0)
                {
                    context.Entry(item).State = EntityState.Added;
                    context.HIJOS.Add(item);
                }
                
                item.MatrimonioId = matrimonio.Id;                
            }

            string matri, matrici;
            if (!matrimonio.FechaMatrimonio.HasValue)
                matri = "null";
            else
                matri = "'" + matrimonio.FechaMatrimonio.Value.Date + "'";

            if (!matrimonio.FechaMatrimonioCivil.HasValue)
                matrici = "null";
            else
                matrici = "'" + matrimonio.FechaMatrimonioCivil.Value.Date + "'";

            var query = "UPDATE MATRIMONIO SET EsposoId = " + matrimonio.EsposoId + ", EsposaId = " + matrimonio.EsposaId + ", FechaMatrimonio = " + matri + ", FechaMatrimonioCivil = " + matrici + " WHERE Id = " + matrimonio.Id;

            context.Database.ExecuteSqlCommand(query);
            context.SaveChanges();
        }

        public void RemoveMatrimonio(int id)
        {
            throw new NotImplementedException();
        }


        public MATRIMONIO GetFromMatrimonioById(int id)
        {
            var query = from c in context.MATRIMONIOs.Include("PERSONA").Include("PERSONA1").Include("HIJOS.PERSONA")
                        where c.Id.Equals(id)
                        select c;
            return query.SingleOrDefault();
        }
    }
}
